package com.hddznet.uniplatform.sm.util;

import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.hddznet.uniplatform.sm.constant.QueryRuleEnum;
import com.hddznet.uniplatform.sm.vo.ConditionReq;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class HdCustomQueryUtils {
	
	public static final String SQL_RULES_COLUMN = "SQL_RULES_COLUMN";
	
	private static final String BEGIN = "_begin";
	private static final String END = "_end";
	private static final String STAR = "*";
	private static final String COMMA = ",";
	private static final String NOT_EQUAL = "!";
	
	/**页面带有规则值查询，空格作为分隔符*/
	private static final String QUERY_SEPARATE_KEYWORD = " ";
	
	/** 排序列 */
	private static final String ORDER_COLUMN = "column";
	/** 排序方式 */
	private static final String ORDER_TYPE = "order";
	private static final String ORDER_TYPE_ASC = "ASC";

	private final static String xssStr = "'|and |exec |insert |select |delete |update |drop |count |chr |mid |master |truncate |char |declare |;|or |+|,";

	/**时间格式化 */
	private static final ThreadLocal<SimpleDateFormat> local = new ThreadLocal<SimpleDateFormat>();
	private static SimpleDateFormat getTime(){
		SimpleDateFormat time = local.get();
		if(time == null){
			time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			local.set(time);
		}
		return time;
	}
	
	public static QueryWrapper<?> parseWhereSql(List<ConditionReq> conditions) {
		QueryWrapper<?> queryWrapper = new QueryWrapper<>();
		if (CollectionUtils.isNotEmpty(conditions)) {
			for (ConditionReq conditionVo : conditions) {
				switch (conditionVo.getOperate()) {
				case "eq":
					queryWrapper.eq(conditionVo.getField(), conditionVo.getValue());
					break;
				case "ne":
					queryWrapper.ne(conditionVo.getField(), conditionVo.getValue());
					break;
				case "like":
					queryWrapper.like(conditionVo.getField(), conditionVo.getValue());
					break;
				case "leftlike":
					queryWrapper.likeLeft(conditionVo.getField(), conditionVo.getValue());
					break;
				case "rightlike":
					queryWrapper.likeRight(conditionVo.getField(), conditionVo.getValue());
					break;
				case "notlike":
					queryWrapper.notLike(conditionVo.getField(), conditionVo.getValue());
					break;
				case "gt":
					queryWrapper.gt(conditionVo.getField(), conditionVo.getValue());
					break;
				case "lt":
					queryWrapper.lt(conditionVo.getField(), conditionVo.getValue());
					break;
				case "ge":
					queryWrapper.ge(conditionVo.getField(), conditionVo.getValue());
					break;
				case "le":
					queryWrapper.le(conditionVo.getField(), conditionVo.getValue());
					break;
				default:
					break;
				}
			}
		}
		return queryWrapper;
	}
	
	public static <T> QueryWrapper<T> initQueryWrapper(T searchObj, Map<String, String[]> parameterMap) {
		long start = System.currentTimeMillis();
		QueryWrapper<T> queryWrapper = new QueryWrapper<T>();
		installMplus(queryWrapper, searchObj, parameterMap);
		log.info("---查询条件构造器初始化完成,耗时:" + (System.currentTimeMillis() - start) + "毫秒----");
		return queryWrapper;
	}

	/**
	 * 区间条件组装 模糊查询 高级查询组装 简单排序 权限查询
	 * 
	 * @param queryWrapper
	 * @param searchObj 对象
	 * @param parameterMap url参数
	 */
	private static void installMplus(QueryWrapper<?> queryWrapper, Object searchObj, Map<String, String[]> parameterMap) {
		PropertyDescriptor origDescriptors[] = PropertyUtils.getPropertyDescriptors(searchObj);
		String name, type;
		for (int i = 0; i < origDescriptors.length; i++) {
			//String aliasName = origDescriptors[i].getName();
			name = origDescriptors[i].getName();
			type = origDescriptors[i].getPropertyType().toString();
			
			Object value = null;
			try {
				// 添加 判断是否有区间值
				String endValue = null,beginValue = null;
				if (parameterMap != null && parameterMap.containsKey(name + BEGIN)) {
					beginValue = parameterMap.get(name + BEGIN)[0].trim();
					addQueryByRule(queryWrapper, name, type, beginValue, QueryRuleEnum.GE);
					
				}
				if (parameterMap != null && parameterMap.containsKey(name + END)) {
					endValue = parameterMap.get(name + END)[0].trim();
					addQueryByRule(queryWrapper, name, type, endValue, QueryRuleEnum.LE);
				}
				value = PropertyUtils.getSimpleProperty(searchObj, name);
			} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | ParseException e) {
				log.error("自定义查询字段{}解析错误", name, e);
			}
			QueryRuleEnum rule = convert2Rule(value);
			value = replaceValue(rule,value);
			
			addEasyQuery(queryWrapper, name, rule, value);
			
		}
		
		// 排序逻辑 处理
		doMultiFieldsOrder(queryWrapper, parameterMap);

	}
	
	/**
	 * 替换掉关键字字符
	 * 
	 * @param rule
	 * @param value
	 * @return
	 */
	private static Object replaceValue(QueryRuleEnum rule, Object value) {
		if (rule == null) {
			return null;
		}
		if (! (value instanceof String)){
			return value;
		}
		String val = (value + "").toString().trim();
		if (rule == QueryRuleEnum.LIKE) {
			value = val.substring(1, val.length() - 1);
		} else if (rule == QueryRuleEnum.LEFT_LIKE || rule == QueryRuleEnum.NE) {
			value = val.substring(1);
		} else if (rule == QueryRuleEnum.RIGHT_LIKE) {
			value = val.substring(0, val.length() - 1);
		} else if (rule == QueryRuleEnum.IN) {
			value = val.split(",");
		} else {
			//update-begin--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
			if(val.startsWith(rule.getValue())){
				//TODO 此处逻辑应该注释掉-> 如果查询内容中带有查询匹配规则符号，就会被截取的（比如：>=您好）
				value = val.replaceFirst(rule.getValue(),"");
			}else if(val.startsWith(rule.getCondition()+QUERY_SEPARATE_KEYWORD)){
				value = val.replaceFirst(rule.getCondition()+QUERY_SEPARATE_KEYWORD,"").trim();
			}
			//update-end--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
		}
		return value;
	}
	
	private static void addQueryByRule(QueryWrapper<?> queryWrapper,String name,String type,String value,QueryRuleEnum rule) throws ParseException {
		if(StringUtils.isNotEmpty(value)) {
			Object temp;
			switch (type) {
			case "class java.lang.Integer":
				temp =  Integer.parseInt(value);
				break;
			case "class java.math.BigDecimal":
				temp =  new BigDecimal(value);
				break;
			case "class java.lang.Short":
				temp =  Short.parseShort(value);
				break;
			case "class java.lang.Long":
				temp =  Long.parseLong(value);
				break;
			case "class java.lang.Float":
				temp =   Float.parseFloat(value);
				break;
			case "class java.lang.Double":
				temp =  Double.parseDouble(value);
				break;
			case "class java.util.Date":
				temp = getDateQueryByRule(value, rule);
				break;
			default:
				temp = value;
				break;
			}
			addEasyQuery(queryWrapper, name, rule, temp);
		}
	}
	
	/**
	 * 获取日期类型的值
	 * @param value
	 * @param rule
	 * @return
	 * @throws ParseException
	 */
	private static Date getDateQueryByRule(String value,QueryRuleEnum rule) throws ParseException {
		Date date = null;
		if(value.length()==10) {
			if(rule==QueryRuleEnum.GE) {
				//比较大于
				date = getTime().parse(value + " 00:00:00");
			}else if(rule==QueryRuleEnum.LE) {
				//比较小于
				date = getTime().parse(value + " 23:59:59");
			}
			//TODO 日期类型比较特殊 可能oracle下不一定好使
		}
		if(date==null) {
			date = getTime().parse(value);
		}
		return date;
	}
	
	/**
	 * 根据所传的值 转化成对应的比较方式
	 * 支持><= like in !
	 * @param value
	 * @return
	 */
	private static QueryRuleEnum convert2Rule(Object value) {
		// 避免空数据
		if (value == null) {
			return null;
		}
		String val = (value + "").toString().trim();
		if (val.length() == 0) {
			return null;
		}
		QueryRuleEnum rule =null;

		//update-begin--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284-------------------
		//TODO 此处规则，只适用于 le lt ge gt
		// step 2 .>= =<
		if (rule == null && val.length() >= 3) {
			if(QUERY_SEPARATE_KEYWORD.equals(val.substring(2, 3))){
				rule = QueryRuleEnum.getByValue(val.substring(0, 2));
			}
		}
		// step 1 .> <
		if (rule == null && val.length() >= 2) {
			if(QUERY_SEPARATE_KEYWORD.equals(val.substring(1, 2))){
				rule = QueryRuleEnum.getByValue(val.substring(0, 1));
			}
		}
		//update-end--Author:scott  Date:20190724 for：initQueryWrapper组装sql查询条件错误 #284---------------------

		// step 3 like
		if (rule == null && val.contains(STAR)) {
			if (val.startsWith(STAR) && val.endsWith(STAR)) {
				rule = QueryRuleEnum.LIKE;
			} else if (val.startsWith(STAR)) {
				rule = QueryRuleEnum.LEFT_LIKE;
			} else if(val.endsWith(STAR)){
				rule = QueryRuleEnum.RIGHT_LIKE;
			}
		}
		// step 4 in
		if (rule == null && val.contains(COMMA)) {
			//TODO in 查询这里应该有个bug  如果一字段本身就是多选 此时用in查询 未必能查询出来
			rule = QueryRuleEnum.IN;
		}
		// step 5 != 
		if(rule == null && val.startsWith(NOT_EQUAL)){
			rule = QueryRuleEnum.NE;
		}
		return rule != null ? rule : QueryRuleEnum.EQ;
	}
	
	/**
	  * 根据规则走不同的查询
	 * @param queryWrapper QueryWrapper
	 * @param name         字段名字
	 * @param rule         查询规则
	 * @param value        查询条件值
	 */
	private static void addEasyQuery(QueryWrapper<?> queryWrapper, String name, QueryRuleEnum rule, Object value) {
		if (value == null || rule == null || Objects.isNull(value)) {
			return;
		}
		name = HdStringUtils.camelToUnderline(name);
		log.info("--查询规则-->"+name+" "+rule.getValue()+" "+value);
		switch (rule) {
		case GT:
			queryWrapper.gt(name, value);
			break;
		case GE:
			queryWrapper.ge(name, value);
			break;
		case LT:
			queryWrapper.lt(name, value);
			break;
		case LE:
			queryWrapper.le(name, value);
			break;
		case EQ:
			queryWrapper.eq(name, value);
			break;
		case NE:
			queryWrapper.ne(name, value);
			break;
		case IN:
			if(value instanceof String) {
				queryWrapper.in(name, (Object[])value.toString().split(","));
			}else if(value instanceof String[]) {
				queryWrapper.in(name, (Object[]) value);
			}else {
				queryWrapper.in(name, value);
			}
			break;
		case LIKE:
			queryWrapper.like(name, value);
			break;
		case LEFT_LIKE:
			queryWrapper.likeLeft(name, value);
			break;
		case RIGHT_LIKE:
			queryWrapper.likeRight(name, value);
			break;
		default:
			log.info("--查询规则未匹配到---");
			break;
		}
	}

	/**
	 * 多字段排序
	 * @param queryWrapper
	 * @param parameterMap
	 */
	private static void doMultiFieldsOrder(QueryWrapper<?> queryWrapper, Map<String, String[]> parameterMap) {
		String column = null, order = null;
		if (parameterMap != null && parameterMap.containsKey(ORDER_COLUMN)) {
			column = parameterMap.get(ORDER_COLUMN)[0];
		}
		if (parameterMap != null && parameterMap.containsKey(ORDER_TYPE)) {
			order = parameterMap.get(ORDER_TYPE)[0];
		}
		log.debug("排序规则>>列:" + column + ",排序方式:" + order);
		if (StringUtils.isNotEmpty(column) && StringUtils.isNotEmpty(order)) {

			// SQL注入check
			filterContent(column);

			if (order.toUpperCase().indexOf(ORDER_TYPE_ASC) >= 0) {
				queryWrapper.orderByAsc(HdStringUtils.camelToUnderline(column));
			} else {
				queryWrapper.orderByDesc(HdStringUtils.camelToUnderline(column));
			}
		}
	}

	private static boolean judgedIsUselessField(String name) {
		return "class".equals(name) || "ids".equals(name) || "page".equals(name) || "rows".equals(name)
				|| "sort".equals(name) || "order".equals(name);
	}

	/**
	 * sql注入过滤处理，遇到注入关键字抛异常
	 * 
	 * @param value
	 * @return
	 */
	private static void filterContent(String value) {
		if (value == null || "".equals(value)) {
			return;
		}
		// 统一转为小写
		value = value.toLowerCase();
		String[] xssArr = xssStr.split("\\|");
		for (int i = 0; i < xssArr.length; i++) {
			if (value.indexOf(xssArr[i]) > -1) {
				log.error("请注意，存在SQL注入关键词---> {}", xssArr[i]);
				log.error("请注意，值可能存在SQL注入风险!---> {}", value);
				throw new RuntimeException("请注意，值可能存在SQL注入风险!--->" + value);
			}
		}
		return;
	}
}
